﻿using AchieveBLL;
using AchieveCommon;
using AchieveEntity;
using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Reporting.WebForms;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using AchieveManageWeb.Models;
using System.Xml;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;
//create by Ben
//Modification time:2019.3.20

namespace AchieveManageWeb.Controllers
{
    [AchieveManageWeb.App_Start.JudgmentLogin]
    public class RptController : Controller
    {
        public ActionResult RdlcReport(string code)
        {
            //LocalReport localReport = new LocalReport();
            //EasyMan.Dtos.ErrorInfo err = new EasyMan.Dtos.ErrorInfo();
            //err.IsError = false;
            try
            {
                
                #region
                string xml = @"<?xml version=""1.0"" encoding=""utf-8""?>
                <Report xmlns:rd=""http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"" xmlns:cl=""http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition"" xmlns=""http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition"">
                  <AutoRefresh>0</AutoRefresh>
                  <DataSources>
                    <DataSource Name=""DummyDataSource"">
                      <ConnectionProperties>
                        <DataProvider>System.Data.DataSet</DataProvider>
                        <ConnectString>/* Local Connection */</ConnectString>
                      </ConnectionProperties>
                      <rd:DataSourceID>31131605-8877-466c-bee6-e8f30eff39f3</rd:DataSourceID>
                    </DataSource>
                  </DataSources>
                  <DataSets>
                    <DataSet Name=""DataSet1"">
                      <Query>
                        <DataSourceName>DummyDataSource</DataSourceName>
                        <CommandText>/* Local Query */</CommandText>
                      </Query>
                      <Fields>
                        <Field Name=""ID"">
                          <DataField>ID</DataField>
                          <rd:TypeName>Decimal</rd:TypeName>
                        </Field>
                        <Field Name=""USER_NAME"">
                          <DataField>USER_NAME</DataField>
                          <rd:TypeName>String</rd:TypeName>
                        </Field>
                        <Field Name=""IS_LOCKOUT_ENABLED"">
                          <DataField>IS_LOCKOUT_ENABLED</DataField>
                          <rd:TypeName>Int16</rd:TypeName>
                        </Field>
                        <Field Name=""PHONE_NUMBER"">
                          <DataField>PHONE_NUMBER</DataField>
                          <rd:TypeName>String</rd:TypeName>
                        </Field>
                        <Field Name=""CREATE_TIME"">
                          <DataField>CREATE_TIME</DataField>
                          <rd:TypeName>DateTime</rd:TypeName>
                        </Field>
                      </Fields>
                      <rd:DataSetInfo>
                        <rd:DataSetName>DummyDataSource</rd:DataSetName>
                        <rd:SchemaPath />
                        <rd:TableName>DataTable1</rd:TableName>
                        <rd:TableAdapterFillMethod />
                        <rd:TableAdapterGetDataMethod />
                        <rd:TableAdapterName />
                      </rd:DataSetInfo>
                    </DataSet>
                  </DataSets>
                  <ReportSections>
                    <ReportSection>
                      <Body>
                        <ReportItems>
                          <Tablix Name=""Tablix1"">
                            <TablixBody>
                              <TablixColumns>
                                <TablixColumn>
                                  <Width>2.5cm</Width>
                                </TablixColumn>
                                <TablixColumn>
                                  <Width>2.5cm</Width>
                                </TablixColumn>
                                <TablixColumn>
                                  <Width>2.5cm</Width>
                                </TablixColumn>
                                <TablixColumn>
                                  <Width>2.5cm</Width>
                                </TablixColumn>
                                <TablixColumn>
                                  <Width>2.5cm</Width>
                                </TablixColumn>
                              </TablixColumns>
                              <TablixRows>
                                <TablixRow>
                                  <Height>0.6cm</Height>
                                  <TablixCells>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""Textbox1"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>ID</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>Textbox1</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""Textbox3"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>USER NAME</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>Textbox3</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""Textbox5"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>IS LOCKOUT ENABLED</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>Textbox5</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""Textbox7"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>PHONE NUMBER</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>Textbox7</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""Textbox9"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>CREATE TIME</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>Textbox9</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                  </TablixCells>
                                </TablixRow>
                                <TablixRow>
                                  <Height>0.6cm</Height>
                                  <TablixCells>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""ID"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>=Fields!ID.Value</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>ID</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""USER_NAME"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>=Fields!USER_NAME.Value</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>USER_NAME</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""IS_LOCKOUT_ENABLED"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>=Fields!IS_LOCKOUT_ENABLED.Value</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>IS_LOCKOUT_ENABLED</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""PHONE_NUMBER"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>=Fields!PHONE_NUMBER.Value</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>PHONE_NUMBER</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                    <TablixCell>
                                      <CellContents>
                                        <Textbox Name=""CREATE_TIME"">
                                          <CanGrow>true</CanGrow>
                                          <KeepTogether>true</KeepTogether>
                                          <Paragraphs>
                                            <Paragraph>
                                              <TextRuns>
                                                <TextRun>
                                                  <Value>=Fields!CREATE_TIME.Value</Value>
                                                  <Style />
                                                </TextRun>
                                              </TextRuns>
                                              <Style />
                                            </Paragraph>
                                          </Paragraphs>
                                          <rd:DefaultName>CREATE_TIME</rd:DefaultName>
                                          <Style>
                                            <Border>
                                              <Color>LightGrey</Color>
                                              <Style>Solid</Style>
                                            </Border>
                                            <PaddingLeft>2pt</PaddingLeft>
                                            <PaddingRight>2pt</PaddingRight>
                                            <PaddingTop>2pt</PaddingTop>
                                            <PaddingBottom>2pt</PaddingBottom>
                                          </Style>
                                        </Textbox>
                                      </CellContents>
                                    </TablixCell>
                                  </TablixCells>
                                </TablixRow>
                              </TablixRows>
                            </TablixBody>
                            <TablixColumnHierarchy>
                              <TablixMembers>
                                <TablixMember />
                                <TablixMember />
                                <TablixMember />
                                <TablixMember />
                                <TablixMember />
                              </TablixMembers>
                            </TablixColumnHierarchy>
                            <TablixRowHierarchy>
                              <TablixMembers>
                                <TablixMember>
                                  <KeepWithGroup>After</KeepWithGroup>
                                </TablixMember>
                                <TablixMember>
                                  <Group Name=""详细信息"" />
                                </TablixMember>
                              </TablixMembers>
                            </TablixRowHierarchy>
                            <DataSetName>DataSet1</DataSetName>
                            <Height>1.2cm</Height>
                            <Width>12.5cm</Width>
                            <Style>
                              <Border>
                                <Style>None</Style>
                              </Border>
                            </Style>
                          </Tablix>
                        </ReportItems>
                        <Height>2in</Height>
                        <Style />
                      </Body>
                      <Width>6.5in</Width>
                      <Page>
                        <PageHeight>29.7cm</PageHeight>
                        <PageWidth>21cm</PageWidth>
                        <LeftMargin>2cm</LeftMargin>
                        <RightMargin>2cm</RightMargin>
                        <TopMargin>2cm</TopMargin>
                        <BottomMargin>2cm</BottomMargin>
                        <ColumnSpacing>0.13cm</ColumnSpacing>
                        <Style />
                      </Page>
                    </ReportSection>
                  </ReportSections>
                  <rd:ReportUnitType>Cm</rd:ReportUnitType>
                  <rd:ReportID>74c3f382-2b1e-4b36-aa0f-860307cf4cb1</rd:ReportID>
                </Report>";
                #endregion
                
                LocalReport report = new LocalReport(); 
                 report.EnableExternalImages = true;
                  report.ShowDetailedSubreportMessages = true;
                 XmlDocument XMLDoc = new XmlDocument();
                 XMLDoc.LoadXml(xml);
               // XMLDoc.Load(ReportTemplateFileDirectory + tpl.MasterTplFileName);

                MemoryStream ms = new MemoryStream();
                XMLDoc.Save(ms);
                 //这个地方必须重置指针位置为0否则下面会出错.
                 ms.Position = 0;
                 report.LoadReportDefinition(ms);
                 DataTable dt = new DataTable();

                //var report = _reportAppService.GetReport(code, 0, false);
                //DataTable dt = _reportAppService.GetDataTableFromCode(code, "", ref err);
                //localReport.LoadReportDefinition(GenerateRdlc(xml));
                ReportDataSource reportDataSource = new ReportDataSource("DataSet1", dt);
                report.DataSources.Add(reportDataSource);
                report.Refresh();

                Warning[] warnings;
                string[] streamids;
                string mimeType;
                string encoding;
                string extension;
                string deviceInfo =
                 "<DeviceInfo>" +
                 "<SimplePageHeaders>True</SimplePageHeaders>" +
                 "</DeviceInfo>";
                byte[] bytes = report.Render("Excel", deviceInfo, out mimeType,
    out encoding, out extension, out streamids, out warnings);
                //return new ReportsResult(bytes, mimeType);
                return File(bytes, mimeType);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
     

        public ActionResult report()
        {
            string RptName = Request["RptName"] == null ? "" : Request["RptName"];
            string reportType = Request["format"] == null ? "Image" : Request["format"];
            string deptTb = string.Empty;

          
            //ReportViewer1.LocalReport.DataSources.Clear();
            //ReportViewer1.LocalReport.DisplayName = "前端显示测试报表";
            ////ReportViewer1.LocalReport.LoadReportDefinition(GenerateRdlc(xmlStr));
            //ReportViewer1.LocalReport.ReportPath = @"WebForms\rpt.rdlc";
           
            //ReportViewer1.LocalReport.DataSources.Add(reportDataSource);
           
            ////  ResponseFile(2, "导出文件");//word
            //ResponseFile(3, "导出文件");

          
            LocalReport localReport = new LocalReport();
           // localReport.ReportPath = Server.MapPath("\\WebForms\\rpt.rdlc");
            localReport.ReportPath = @"WebForms\rpt.rdlc";

            DataTable dt = RptBLL.getRptDt2019("gskl");

            ReportDataSource reportDataSource = new ReportDataSource("gsklDS", dt);
            localReport.DataSources.Add(reportDataSource);
            ReportParameter rptitle = new ReportParameter("rptitle", "前端显示测试报表");
            localReport.SetParameters(new ReportParameter[] { rptitle });

            Warning[] warnings;
            string[] streams;
            byte[] renderedBytes;
            string mimeType;
            string encoding;
            string fileNameExtension;
           // string reportType = "Image";
            string deviceInfo =
            "<DeviceInfo>" +
            "  <OutputFormat>" + reportType + "</OutputFormat>" +
            "  <PageWidth>8.5in</PageWidth>" +
            "  <PageHeight>11in</PageHeight>" +
            "  <MarginTop>0.5in</MarginTop>" +
            "  <MarginLeft>1in</MarginLeft>" +
            "  <MarginRight>1in</MarginRight>" +
            "  <MarginBottom>0.5in</MarginBottom>"+
            "</DeviceInfo>";
            deviceInfo = null;
            //if (format.ToLower() == "image")
            //{
            //    double inchValue = (dt.Count / 37.0) * 11;
            //    deviceInfo += string.Format("  <PageHeight>{0}in</PageHeight>", inchValue);
            //}
            //else
            //{
            //    deviceInfo += "  <PageHeight>11in</PageHeight>";
            //}

            renderedBytes = localReport.Render(
                reportType,
                deviceInfo,
                out mimeType,
                out encoding,
                out fileNameExtension,
                out streams,
                out warnings);
           
            //return File(renderedBytes, (reportType.ToLower() == "image") ? "image/jpeg" : mimeType);
            if (reportType.ToLower() == "image")
            {
                //string jpegFileName = "rptpic";
                System.IO.MemoryStream ms = new System.IO.MemoryStream(renderedBytes);
                System.Drawing.Image img = System.Drawing.Image.FromStream(ms);


               // var img = Image.FromFile(tiffFileName);
                var count = img.GetFrameCount(FrameDimension.Page);
                //for (int i = 0; i < count; i++)
                //{
                //    img.SelectActiveFrame(FrameDimension.Page, i);
                //    img.Save(jpegFileName + ".part" + i + ".jpg");
                //}
                int imageWidth = img.Width;
                int imageHeight = img.Height;
                //int imageHeight = img.Height * count;
                Bitmap joinedBitmap = new Bitmap(imageWidth, imageHeight);
                Graphics graphics = Graphics.FromImage(joinedBitmap);
                graphics.DrawImage(img, 0, 0, img.Width, img.Height);

                //for (int i = 0; i < count; i++)
                //{
                //    var partImageFileName = jpegFileName + ".part" + i + ".jpg";
                //    Image partImage = Image.FromFile(partImageFileName);
                //    graphics.DrawImage(partImage, 0, partImage.Height * i, partImage.Width, partImage.Height);
                //    partImage.Dispose();
                //    File.Delete(partImageFileName);
                //}
                MemoryStream mstream = new MemoryStream();
                joinedBitmap.Save(mstream, System.Drawing.Imaging.ImageFormat.Bmp);
                //byte[] byData = new Byte[mstream.Length];
                mstream.Position = 0;
                //mstream.Read(byData, 0, byData.Length);
                //mstream.Close();
                //return File(byData, "image/GIF");
                return File(mstream, "image/GIF");
                //return byData;

                //Response.ContentType = "image/GIF";
                ////图片输出的类型有: image/GIF     image/JPEG
                //Response.BinaryWrite(streamByte);

                //joinedBitmap.Save(jpegFileName);

                //graphics.Dispose();
                //joinedBitmap.Dispose();
                //img.Dispose();
            }
            else
            {
                return File(renderedBytes, mimeType);
            }

         


            
        }
        //
        // GET: / /
        public ActionResult Index()
        {
            AchieveManageWeb.Models.ActionFilters.LoggerHelper.Notes(new LogContent(ViewData, "访问视图", "报表index")); //日志记录
            return View();
        }
        public ActionResult gskl()
        {
            AchieveManageWeb.Models.ActionFilters.LoggerHelper.Notes(new LogContent(ViewData, "访问视图", "报表-钢水控流")); //日志记录
            return View();
        }
        public ActionResult yjgc()
        {
            AchieveManageWeb.Models.ActionFilters.LoggerHelper.Notes(new LogContent(ViewData, "访问视图", "报表-冶金工程")); //日志记录
            return View();
        }
        public ActionResult nyhj()
        {
            AchieveManageWeb.Models.ActionFilters.LoggerHelper.Notes(new LogContent(ViewData, "访问视图", "报表-能源环境")); //日志记录
            return View();
        }
        public ActionResult dljt()
        {
            AchieveManageWeb.Models.ActionFilters.LoggerHelper.Notes(new LogContent(ViewData, "访问视图", "报表-电缆卷筒")); //日志记录
            return View();
        }
        public ActionResult zzb()
        {
            AchieveManageWeb.Models.ActionFilters.LoggerHelper.Notes(new LogContent(ViewData, "访问视图", "报表-综合部")); //日志记录
            return View();
        }
        /// <summary>
        /// 轨道交通
        /// </summary>
        /// <returns></returns>
        public ActionResult gdjt()
        {
            AchieveManageWeb.Models.ActionFilters.LoggerHelper.Notes(new LogContent(ViewData, "访问视图", "报表-轨道交通")); //日志记录
            return View();
        }
        /// <summary>
        /// 公司汇总
        /// </summary>
        /// <returns></returns>
        public ActionResult gshz()
        {
            AchieveManageWeb.Models.ActionFilters.LoggerHelper.Notes(new LogContent(ViewData, "访问视图", "报表-公司汇总")); //日志记录
            return View();
        }

        /// <summary>
        /// 获取部门考核报表数据--通用查询；输入参数RptName,支持五个部门
        /// </summary>
        /// <returns></returns>
        public ActionResult GetRptDT()
        {
            //obtain the query para
            string RptName = Request["RptName"] == null ? "" : Request["RptName"];
            #region old method
            //            string deptTb=string.Empty;
//            switch (RptName)
//            {
//                case    "gskl"://钢水控流
//                    deptTb = "rpt01";
//                    break;
//                case    "yjgc"://冶金工程
//                    deptTb = "rpt02";
//                    break;
//                case    "nyhj":
//                    deptTb = "rpt03";//能源环境
//                    break;
//                case    "dljt":
//                    deptTb = "rpt06";//电缆卷筒
//                    break;
//                case    "zzb"://制造部
//                    deptTb = "rpt05";
//                    break;             

//                default:
//                    break;

//            }

//            DataTable dt = new DataTable();
//            string strSql = @"select rowid=30, 科目,isNull(一月,0)as 一月,isNull(二月,0)as 二月,isNull(三月,0)as 三月,isNull(四月,0)as 四月,
//                            isNull(五月,0)as 五月,isNull(六月,0)as 六月,isNull(七月,0)as 七月,isNull(八月,0)as 八月,isNull(九月,0)as 九月,
//                                isNull(十月,0)as 十月,isNull(十一月,0)as 十一月,isNull(十二月,0)as 十二月 from "+deptTb;

//            dt = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, strSql.ToString());

//            #region 初始化报表数据视图
//            for (int i = 0; i < dt.Rows.Count; i++)
//            {
//                switch (dt.Rows[i]["科目"].ToString().Trim())
//                {
//                    case "开票金额":
//                        dt.Rows[i]["rowid"] = 0;
//                        break;
//                    case "回款金额":
//                        dt.Rows[i]["rowid"] = 1;
//                        break;
//                    case "发货金额":
//                        dt.Rows[i]["rowid"] = 2;                       
//                        break;
//                    case "直接材料":
//                        dt.Rows[i]["rowid"] = 4;
//                        break;
//                    case "直接人工":
//                        dt.Rows[i]["rowid"] = 5;
//                        break;
//                    case "易耗品":
//                        dt.Rows[i]["rowid"] = 6;
//                        break;
//                    case "运输费":
//                        dt.Rows[i]["rowid"] = 7;
//                        break;
//                    case "外协费":
//                        dt.Rows[i]["rowid"] = 8;
//                        break;
//                    case "部门工资":
//                        dt.Rows[i]["rowid"] = 12;
//                        break;
//                    case "办公费":
//                        dt.Rows[i]["rowid"] = 13;
//                        break;
//                    case "差旅费":
//                        dt.Rows[i]["rowid"] = 14;
//                        break;
//                    case "招待费":
//                        dt.Rows[i]["rowid"] = 15;
//                        break;
//                    case "研发费":
//                        dt.Rows[i]["rowid"] = 21;
//                        break;
//                    case "退款金额":
//                        dt.Rows[i]["rowid"] = 22;
//                        break;
//                    case "回款退款":
//                        if (RptName=="zzb")
//                        {
//                            dt.Rows[i]["rowid"] = 22;
//                        }                        
//                        break;
//                    case "付款金额":
//                        dt.Rows[i]["rowid"] = 23;
//                        break;
//                    default:
//                       // dt.Rows[i]["rowid"] =30;
//                        break;
//                }
//            }

//            if (RptName == "zzb")//制造部需分别合并两项办公费、两项差旅费
//            {
//                for (int i = 0; i < dt.Rows.Count; i++)
//                {
//                    if (Convert.ToInt32(dt.Rows[i]["rowid"]) == 13)//办公费
//                    {
//                        for (int j = i + 1; j < dt.Rows.Count; j++)
//                        {
//                            if (Convert.ToInt32(dt.Rows[j]["rowid"]) == 13)//第二个办公费
//                            {
//                                //累加到第一行
//                                for (int k = 2; k < 14; k++)
//                                {
//                                    dt.Rows[i][k] = (decimal)dt.Rows[i][k] + (decimal)dt.Rows[j][k];
//                                }
//                                //删除第二行
//                                dt.Rows.RemoveAt(j);
//                                break;
//                            }
//                        }
//                        break;
//                    }
//                }
//                for (int i = 0; i < dt.Rows.Count; i++)
//                {
//                    if (Convert.ToInt32(dt.Rows[i]["rowid"]) == 14)//差旅费
//                    {
//                        for (int j = i + 1; j < dt.Rows.Count; j++)
//                        {
//                            if (Convert.ToInt32(dt.Rows[j]["rowid"]) == 14)//第二个差旅费
//                            {
//                                //累加到第一行
//                                for (int k = 2; k < 14; k++)
//                                {
//                                    dt.Rows[i][k] = (decimal)dt.Rows[i][k] + (decimal)dt.Rows[j][k];
//                                }
//                                //删除第二行
//                                dt.Rows.RemoveAt(j);
//                                break;
//                            }
//                        }
//                        break;
//                    }
//                }
//            }

//            dt.Rows.Add(3, "销售金额");
//            dt.Rows.Add(9, "制造费用");
//            dt.Rows.Add(10, "项目利润");
//            dt.Rows.Add(11, "项目利润率");
//            dt.Rows.Add(16, "部门费用");
//            dt.Rows.Add(17, "研发费用");
//            if (RptName == "gskl" | RptName == "yjgc" | RptName == "nyhj")//三个需要计算公摊费用的部门
//            {
//                string sql = "select 18 as rowid,'公摊费用' as 科目, sum(一月)/3 as 一月,sum(二月)/3 as 二月,sum(三月)/3 as 三月,sum(四月)/3 as 四月,sum(五月)/3 as 五月,sum(六月)/3 as 六月,sum(七月)/3 as 七月,sum(八月)/3 as 八月,sum(九月)/3 as 九月,sum(十月)/3 as 十月,sum(十一月)/3 as 十一月,sum(十二月)/3 as 十二月 from rpt04 WHERE 科目 not in('综合部付款','补贴收入','营业外收入')";
//                DataTable dtf = SqlHelper.GetDataTable(SqlHelper.connStrK3, sql);
//                dt.Rows.Add(dtf.Rows[0].ItemArray);
//            }
//            else
//            {
//                dt.Rows.Add(18, "公摊费用", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
//            }
            
//            dt.Rows.Add(19, "利润");
//            dt.Rows.Add(20, "利润率");
          
//            DataView dataView = dt.DefaultView;//利用视图排序
//            dataView.Sort = "rowid asc";
//            dt = dataView.ToTable();//排序完毕
//            dt.Columns.Add("全年", typeof(Decimal));//添加全年统计列

//            #endregion

//            #region 报表月数据公式计算
//            for (int i = 2; i < 14; i++)//选择列，0：id；1：科目；2：一月...
//            {
//                #region//计算研发比率和订单比例
//                decimal yfRate = 0;
//                decimal ddRate = 1;
//                try
//                {
//                    decimal rate = Convert.ToDecimal(dt.Rows[21][i]) / (Convert.ToDecimal(dt.Rows[4][i]) + Convert.ToDecimal(dt.Rows[6][i]));//   研发费/(直接材料+易耗品)>1 

//                    if (rate > 1)
//                    {
//                        yfRate = 1;
//                    }
//                    else
//                    {
//                        yfRate = rate;
//                    }
//                }
//                catch (Exception)
//                {

//                    yfRate = 0;
//                }
//                ddRate = 1- yfRate;
//                #endregion
//                //00计算原始制造费用，原始制造费用：=SUM(4:8)，仅用于研发费用计算！
//               decimal ozzfy = Convert.ToDecimal(dt.Rows[4][i]) + Convert.ToDecimal(dt.Rows[5][i]) + Convert.ToDecimal(dt.Rows[6][i]) + Convert.ToDecimal(dt.Rows[7][i]) + Convert.ToDecimal(dt.Rows[8][i]);
//                //0，开票金额：原始数据
//                //1，回款金额:回款金额+退款金额
//                dt.Rows[1][i] =  Convert.ToDecimal(dt.Rows[1][i]) + Convert.ToDecimal( dt.Rows[22][i]);
//                //2,发货金额：发货金额/1.16
//                dt.Rows[2][i] = Convert.ToDecimal(dt.Rows[2][i]) / 1.16m;
//                //3,销售金额:
//                dt.Rows[3][i] = Convert.ToDecimal(dt.Rows[0][i]) * 0.2m + Convert.ToDecimal(dt.Rows[1][i]) / 1.16m * 0.3m + Convert.ToDecimal(dt.Rows[2][i]) * 0.5m;
//                //4,直接材料:直接材料*订单比例
//                dt.Rows[4][i] = Convert.ToDecimal(dt.Rows[4][i]) *ddRate;
//                //5.直接人工:直接人工*订单比例
//                dt.Rows[5][i] = Convert.ToDecimal(dt.Rows[5][i]) * ddRate;
//                //6.易耗品:易耗品*订单比例
//                dt.Rows[6][i] = Convert.ToDecimal(dt.Rows[6][i]) * ddRate;
//                //7.运输费:运输费*订单比例
//                dt.Rows[7][i] = Convert.ToDecimal(dt.Rows[7][i]) * ddRate;
//                //8.外协费:外协费*订单比例
//                dt.Rows[8][i] = Convert.ToDecimal(dt.Rows[8][i]) * ddRate;
//                //9.制造费用：=SUM(4:8)
//                dt.Rows[9][i] = Convert.ToDecimal(dt.Rows[4][i]) + Convert.ToDecimal(dt.Rows[5][i]) + Convert.ToDecimal(dt.Rows[6][i]) + Convert.ToDecimal(dt.Rows[7][i]) + Convert.ToDecimal(dt.Rows[8][i]);
//                //10.项目利润
//                dt.Rows[10][i] = Convert.ToDecimal(dt.Rows[3][i]) - Convert.ToDecimal(dt.Rows[9][i]);
//                //11.项目利润率
//                try
//                {
//                    dt.Rows[11][i] = Convert.ToDecimal(dt.Rows[10][i]) / Convert.ToDecimal(dt.Rows[3][i]);
//                }
//                catch (Exception)
//                {

//                    dt.Rows[11][i] = 0;
//                }
//                //12：15.部门工资、办公费、差旅费、招待费，使用原始数据
//                //16.部门费用，sum(12:15)
//                dt.Rows[16][i] = Convert.ToDecimal(dt.Rows[12][i]) + Convert.ToDecimal(dt.Rows[13][i]) + Convert.ToDecimal(dt.Rows[14][i]) + Convert.ToDecimal(dt.Rows[15][i]);
//                //17.研发费用，原始制造费用*研发比率 
//                dt.Rows[17][i] = ozzfy * yfRate;
                
//                //18 公摊费用,视图初始化中已加载
//                //19 利润,
//                dt.Rows[19][i] = Convert.ToDecimal(dt.Rows[10][i]) - Convert.ToDecimal(dt.Rows[16][i]) - Convert.ToDecimal(dt.Rows[17][i]) - Convert.ToDecimal(dt.Rows[18][i]);
//                //20.计算利润率
//                    try
//                    {
//                        dt.Rows[20][i] = Convert.ToDecimal(dt.Rows[19][i]) / Convert.ToDecimal(dt.Rows[3][i]);
//                    }
//                    catch (Exception)
//                    {

//                        dt.Rows[20][i] = 0;
//                    }

//            }

//            #endregion

//            # region  计算全年统计列           
           
//            for (int i = 0; i < 21; i++)//共20行
//            {
//                if (i == 11)//项目利润率
//                {
//                    decimal lrl = 0;

//                    try
//                    {
//                        lrl = Convert.ToDecimal(dt.Rows[10]["全年"]) / Convert.ToDecimal(dt.Rows[3]["全年"]);
//                    }
//                    catch (Exception)
//                    {

//                        lrl = 0;
//                    }
//                    dt.Rows[i]["全年"] = lrl;
//                }
//                else if (i == 20)//利润率
//                {
//                    decimal lrl = 0;

//                    try
//                    {
//                        lrl = Convert.ToDecimal(dt.Rows[19]["全年"]) / Convert.ToDecimal(dt.Rows[3]["全年"]);
//                    }
//                    catch (Exception)
//                    {

//                        lrl = 0;
//                    }
//                    dt.Rows[i]["全年"] = lrl;
//                }
//                else
//                {
//                    decimal sum = 0;

//                    for (int j = 2; j < 14; j++)
//                    {
//                        sum += Convert.ToDecimal(dt.Rows[i][j]);
//                    }
//                    dt.Rows[i]["全年"] = sum;

//                }

//            }
//            #endregion

//            //删除不需要显示的行数据（科目），保留前21行
//            for (int i = dt.Rows.Count - 1; i > 20; i--)
//            {
//                dt.Rows.RemoveAt(i);
            //            }
            #endregion
            DataTable dt = RptBLL.getRptDt2019(RptName);
            string jsondt = JsonHelper.ToJson(dt);
            return Content(jsondt);
        }

        /// <summary>
        /// 报表查询通用接口控制器
        /// </summary>
        /// <returns></returns>
        public ActionResult GetRptCommon()
        { 
            string RptName = Request["RptName"] == null ? "" : Request["RptName"];
            int year = Request["year"] == null ? 2020 : Convert.ToInt32(Request["year"]);
            DataTable dt = new DataTable();
            switch (RptName)
            {
                case "MonthMOFinishRate":
                    dt = manufactureBLL.MonthMOFinishRate(year);
                    break;
                default:
                    break;
            }
            int totalCount = dt.Rows.Count;
            string strJson = AchieveCommon.JsonHelper.ToJson(dt);
            string content = "{\"total\": " + totalCount.ToString() + ",\"rows\":" + strJson + "}";
            return Content(content);
        }
        /// <summary>
        /// 报表分页查询通用接口控制器
        /// </summary>
        /// <returns></returns>
        public ActionResult GetRptPageCommon()
        {
            string strWhere = "1=1";
            //首先获取前台传递过来的参数
            string sort = Request["sort"] == null ? "Id" : Request["sort"];
            string order = Request["order"] == null ? "asc" : Request["order"];
            int pageindex = Request["page"] == null ? 1 : Convert.ToInt32(Request["page"]);
            int pagesize = Request["rows"] == null ? 20 : Convert.ToInt32(Request["rows"]);
            string RptName = Request["RptName"] == null ? "" : Request["RptName"]; 
            
            DataTable dt = new DataTable();
            int totalCount=0;
            switch (RptName)
            {
                case "finishRateDetail":
                      string Dept = Request["Dept"] == null ? "" : Request["Dept"];
                      string ItemName = Request["ItemName"] == null ? "" : Request["ItemName"];
                      string FBillNo = Request["FBillNo"] == null ? "" : Request["FBillNo"];
                      string FModel = Request["FModel"] == null ? "" : Request["FModel"];
                     string period = Request["period"] == null ? "" : Request["period"];
                     int year = Request["year"] == null ? 2020 : Convert.ToInt32(Request["year"]); 
                     dt = manufactureBLL.finishRateDetail(Dept, year, ItemName, FBillNo, FModel, period, sort, pagesize, pageindex, "", out totalCount); 
                    break;
                default:
                    break;
            } 
            string strJson = AchieveCommon.JsonHelper.ToJson(dt);
            string content = "{\"total\": " + totalCount.ToString() + ",\"rows\":" + strJson + "}";
            return Content(content);
        }


        /// <summary>
        /// 能源环境事业部月报表，2020.4.20
        /// </summary>
        /// <returns></returns>
        public ActionResult nyhjmonthRpt() {
            int year = Request["year"] == null ? 2020 : Convert.ToInt32(Request["year"]);
            string status = Request["status"] == null ? "all" : Request["status"];//all，0，1 
            string periodType = Request["periodType"] == null ? "claimTime" : Request["periodType"];//claimPeriod 
            string rptType = Request["rptType"] == null ? "yysr" : Request["rptType"];//claimPeriod 
            DataTable dt=new DataTable();
            if (rptType=="yysr")
            {
                dt = RptBLL.getNyhjMonthRptDt(year, periodType, status);
            }
            else
            {
                dt = RptBLL.getDeptXSCZMonthRptDt(year, periodType, status, "能源环保事业部");
            }           
            string jsondt = JsonHelper.ToJson(dt);
            return Content(jsondt);
        }
        /// <summary>
        /// 事业部月报表子表-营业收入
        /// </summary>
        /// <returns></returns>
        public ActionResult subMonthRptYYSR()
        {
            string dept = Request["dept"] == null ? "nyhb" : Request["dept"];
            int year = Request["year"] == null ? 2020 : Convert.ToInt32( Request["year"]);
            DataTable dt = RptBLL.getSubMonthRptDt(dept, year,"yysr");
            string jsondt = JsonHelper.ToJson(dt);
            return Content(jsondt);
        }
        /// <summary>
        /// 事业部月报表子表-销售产值
        /// </summary>
        /// <returns></returns>
        public ActionResult subMonthRptXSCZ()
        {
            string dept = Request["dept"] == null ? "nyhb" : Request["dept"];
            int year = Request["year"] == null ? 2020 : Convert.ToInt32(Request["year"]);
            DataTable dt = RptBLL.getSubMonthRptDt(dept, year, "销售产值");
            string jsondt = JsonHelper.ToJson(dt);
            return Content(jsondt);
        }
        /// <summary>
        /// 事业部月报表子表-营业成本
        /// </summary>
        /// <returns></returns>
        public ActionResult subMonthRptYYCB()
        {
            string dept = Request["dept"] == null ? "nyhb" : Request["dept"];
            int year = Request["year"] == null ? 2020 : Convert.ToInt32(Request["year"]);
            DataTable dt = RptBLL.getSubMonthRptYYCB(dept, year);
            string jsondt = JsonHelper.ToJson(dt);
            return Content(jsondt);
        }

        /// <summary>
        /// 获取公司汇总报表数据
        /// </summary>
        /// <returns></returns>
        public ActionResult GetRptDTgshz()
        {
            
            DataTable dt = new DataTable("tb_rpt");//rpt dt
            dt.Columns.Add("rowid", typeof(Int32));
            dt.Columns.Add("科目", typeof(String));
            dt.Columns.Add("钢水", typeof(Decimal));
            dt.Columns["钢水"].DefaultValue = 0;
            dt.Columns.Add("冶金", typeof(Decimal));
            dt.Columns["冶金"].DefaultValue = 0;
            dt.Columns.Add("能源", typeof(Decimal));
            dt.Columns["能源"].DefaultValue = 0;
            dt.Columns.Add("电卷", typeof(Decimal));
            dt.Columns["电卷"].DefaultValue = 0;
            dt.Columns.Add("制造", typeof(Decimal));
            dt.Columns["制造"].DefaultValue = 0;
            dt.Columns.Add("公司", typeof(Decimal));
            dt.Columns["公司"].DefaultValue = 0;
            dt.Columns.Add("车轮", typeof(Decimal));
            dt.Columns["车轮"].DefaultValue = 0;
            dt.Columns.Add("合计", typeof(Decimal));
            dt.Columns["合计"].DefaultValue = 0;
            dt.Rows.Add(0,"开票金额");
            dt.Rows.Add(1, "回款金额");
            dt.Rows.Add(2, "发货金额");
            dt.Rows.Add(3, "销售金额");
            dt.Rows.Add(4, "直接材料");
            dt.Rows.Add(5, "直接人工");
            dt.Rows.Add(6, "易耗品");
            dt.Rows.Add(7, "运输费");
            dt.Rows.Add(8, "外协费");
            dt.Rows.Add(9, "制造费用");
            dt.Rows.Add(10, "项目利润");
            dt.Rows.Add(11, "项目利润率");
            dt.Rows.Add(12, "部门工资");
            dt.Rows.Add(13, "办公室");
            dt.Rows.Add(14, "差旅费");
            dt.Rows.Add(15, "招待费");
            dt.Rows.Add(16, "部门费用");
            dt.Rows.Add(17, "公摊费用");
            dt.Rows.Add(18, "利润");
            dt.Rows.Add(19, "利润率");
            dt.Rows.Add(20, "营业外收入/补贴");
            dt.Rows.Add(21, "车轮分摊");
            dt.Rows.Add(22, "利润");
            dt.Rows.Add(23, "利润率");
            dt.Rows.Add(24, "研发费用");
            dt.Rows.Add(25 , "利润");
            dt.Rows.Add(26, "利润率");
            dt.Rows.Add(27, "制造部付款");
            dt.Rows.Add(28, "综合部付款");
            dt.Rows.Add(29 , "其他部门付款");
            dt.Rows.Add(30,"合计");
            dt.Rows.Add(31, "车轮付款");
            dt.Rows.Add(32, "合计付款");
             
 

            string sql1 = @"select 30 as rowid, FAccountID as kmid,科目 as km,isNull(一月,0)+isNull(二月,0)+isNull(三月,0)+isNull(四月,0)
                            +isNull(五月,0)+isNull(六月,0)+isNull(七月,0)+isNull(八月,0)
                            +isNull(九月,0)+isNull(十月,0)+isNull(十一月,0)+isNull(十二月,0) as summonth from ";
//            DataTable dt_gs = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql1+" rpt01");//钢水控流
//            DataTable dt_yj = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql1 + " rpt02");//冶金
//            DataTable dt_ny = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql1 + " rpt03");//能源环境
//            DataTable dt_dj = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql1 + "rpt06");//电卷
//            DataTable dt_zz = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql1 + " rpt05");//制造部
            DataTable dt_cl = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql1 + " rpt07");//车轮,轨道交通事业部
//            DataTable dt_gt = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql1 + " rpt04");//公摊费用
            RptBLL rb = new RptBLL();
            DataTable dt_gs = rb.tbRptMonth("gskl");//钢水控流
            DataTable dt_yj = rb.tbRptMonth("yjgc");//冶金
            DataTable dt_ny = rb.tbRptMonth("nyhj");//能源环境
            DataTable dt_dj = rb.tbRptMonth("dljt");//电卷
            DataTable dt_zz = rb.tbRptMonth("zzb");//制造部
           // DataTable dt_cl = rb.tbRptMonth("cl");//车轮,轨道交通事业部
           // DataTable dt_gt = SqlHelper.GetDataTable(SqlHelper.connStrK3, CommandType.Text, sql1 + " rpt04");//公摊费用

            DataSet ds = new DataSet();
            ds.Tables.Add(dt_gs);//0
            ds.Tables.Add(dt_yj);//1
            ds.Tables.Add(dt_ny);//2
            ds.Tables.Add(dt_dj);//3
            ds.Tables.Add(dt_zz);//4
    
            //查询公摊费用
          string sql_gtfy="select  sum(一月)+sum(二月)+sum(三月)+sum(四月)+sum(五月)+sum(六月)+sum(七月)+sum(八月)+sum(九月)+sum(十月)+sum(十一月)+sum(十二月) from rpt04 WHERE 科目 not in('综合部付款','补贴收入','营业外收入')";
          object o1 = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql_gtfy);
          decimal gtfy = o1.Equals(DBNull.Value) ? 0 : Convert.ToDecimal(o1); 
            
          //查询营业外补贴收入
          string sql_btsr = " select sum(一月)+sum(二月)+sum(三月)+sum(四月)+sum(五月)+sum(六月)+sum(七月)+sum(八月)+sum(九月)+sum(十月)+sum(十一月)+sum(十二月) as b from rpt04 where FAccountID=1075 or FAccountID=1078";
         
          object o2 = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql_btsr);
          decimal btsr = o1.Equals(DBNull.Value) ? 0 : Convert.ToDecimal(o2); 


            //查询车轮分摊
          string sql_clft = @" select( (
                    select sum(一月)+sum(二月)+sum(三月)+sum(四月)+sum(五月)+sum(六月)+sum(七月)+sum(八月)+sum(九月)+sum(十月)+sum(十一月)+sum(十二月)  
                    from rpt04 where FAccountID in(1148,1255,1081,1256,1284,1088)
                    )-( (select (sum(一月)+sum(二月)+sum(三月)+sum(四月)+sum(五月)+sum(六月)+sum(七月)+sum(八月)+sum(九月)+sum(十月)+sum(十一月)+sum(十二月))*0.28 from rpt04 where FAccountID=1081
		                    )+(
		                    select (sum(一月)+sum(二月)+sum(三月)+sum(四月)+sum(五月)+sum(六月)+sum(七月)+sum(八月)+sum(九月)+sum(十月)+sum(十一月)+sum(十二月))*0.7 from rpt04 where FAccountID=1255
		                    )+(
		                    select (sum(一月)+sum(二月)+sum(三月)+sum(四月)+sum(五月)+sum(六月)+sum(七月)+sum(八月)+sum(九月)+sum(十月)+sum(十一月)+sum(十二月))*0.65 from rpt04 where FAccountID in (1284,1256,1148,1088)
		                    ) )) as clft   ";
          
          object o3 = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql_clft);
          decimal clft = o1.Equals(DBNull.Value) ? 0 : Convert.ToDecimal(o3); 
            
            for (int i = 2; i < 7; i++)//选择列--主表数据填充
            {
                for (int j = 0; j < 17; j++)
                {
                     dt.Rows[j][i] = ds.Tables[i - 2].Rows[j]["全年"];
                }

                if (i < 5)
                {
                    dt.Rows[17][i] = gtfy / 3m;//公摊费用
                    dt.Rows[20][i] = btsr / 3m;//营业外补贴收入
                    dt.Rows[21][i] = clft / 3m;//车轮分摊
                }
                else {
                    dt.Rows[17][i] = 0;// 电卷和制造两个部门不分摊费用
                    dt.Rows[20][i] =0;// 
                    dt.Rows[21][i] = 0;// 
                }              
            
                dt.Rows[18][i] = Convert.ToDecimal(dt.Rows[10][i]) - Convert.ToDecimal(dt.Rows[16][i]) - Convert.ToDecimal(dt.Rows[17][i]);////利润18=10-16-17
                dt.Rows[19][i] = Convert.ToDecimal(dt.Rows[18][i]) / Convert.ToDecimal(dt.Rows[3][i]);//利润率[19]
                dt.Rows[22][i] = Convert.ToDecimal(dt.Rows[21][i]) + Convert.ToDecimal(dt.Rows[20][i]) + Convert.ToDecimal(dt.Rows[18][i]);//利润[22]
                dt.Rows[23][i] = Convert.ToDecimal(dt.Rows[22][i]) / Convert.ToDecimal(dt.Rows[3][i]);//利润率[23]
                dt.Rows[24][i] = ds.Tables[i - 2].Rows[17]["全年"];//源数据表中17行--为研发费用
                dt.Rows[25][i] = Convert.ToDecimal(dt.Rows[22][i]) - Convert.ToDecimal(dt.Rows[24][i]);//利润[25]
                dt.Rows[26][i] = Convert.ToDecimal(dt.Rows[25][i]) / Convert.ToDecimal(dt.Rows[3][i]);//利润率[26]
            }
           string sql_zzbfk = @"select sum(isNull(一月,0)+isNull(二月,0)+isNull(三月,0)+isNull(四月,0)
                                    +isNull(五月,0)+isNull(六月,0)+isNull(七月,0)+isNull(八月,0)
                                    +isNull(九月,0)+isNull(十月,0)+isNull(十一月,0)+isNull(十二月,0)) from rpt05 where FAccountID in (6,7)";
           dt.Rows[27][2] = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql_zzbfk);//制造部付款

            //综合部付款
           string sql_zhbfk = @"select sum(isNull(一月,0)+isNull(二月,0)+isNull(三月,0)+isNull(四月,0)
                                +isNull(五月,0)+isNull(六月,0)+isNull(七月,0)+isNull(八月,0)
                                +isNull(九月,0)+isNull(十月,0)+isNull(十一月,0)+isNull(十二月,0)) from rpt04 where FAccountID in (6)";
           dt.Rows[28][2] = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql_zhbfk);
//其他部门付款
           string sql_qtbmfk = @"select((
                                select sum(isNull(一月,0)+isNull(二月,0)+isNull(三月,0)+isNull(四月,0)
                                +isNull(五月,0)+isNull(六月,0)+isNull(七月,0)+isNull(八月,0)
                                +isNull(九月,0)+isNull(十月,0)+isNull(十一月,0)+isNull(十二月,0)) from rpt01 where FAccountID in (6)
                                )+(
                                select sum(isNull(一月,0)+isNull(二月,0)+isNull(三月,0)+isNull(四月,0)
                                +isNull(五月,0)+isNull(六月,0)+isNull(七月,0)+isNull(八月,0)
                                +isNull(九月,0)+isNull(十月,0)+isNull(十一月,0)+isNull(十二月,0)) from rpt02 where FAccountID in (6)
                                )+(
                                select sum(isNull(一月,0)+isNull(二月,0)+isNull(三月,0)+isNull(四月,0)
                                +isNull(五月,0)+isNull(六月,0)+isNull(七月,0)+isNull(八月,0)
                                +isNull(九月,0)+isNull(十月,0)+isNull(十一月,0)+isNull(十二月,0)) from rpt03 where FAccountID in (6)
                                )+(
                                select sum(isNull(一月,0)+isNull(二月,0)+isNull(三月,0)+isNull(四月,0)
                                +isNull(五月,0)+isNull(六月,0)+isNull(七月,0)+isNull(八月,0)
                                +isNull(九月,0)+isNull(十月,0)+isNull(十一月,0)+isNull(十二月,0)) from rpt06 where FAccountID in (6)
                                ))as ans";
           dt.Rows[29][2] = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql_qtbmfk);
//合计
           dt.Rows[30][2] = Convert.ToDecimal(dt.Rows[28][2]) + Convert.ToDecimal(dt.Rows[27][2]) + Convert.ToDecimal(dt.Rows[29][2]);// 

//车轮付款
           dt.Rows[31][2] = dt_cl.Select("kmid=6")[0]["summonth"];
//           string sql_clfk = @"select sum(isNull(一月,0)+isNull(二月,0)+isNull(三月,0)+isNull(四月,0)
//                                +isNull(五月,0)+isNull(六月,0)+isNull(七月,0)+isNull(八月,0)
//                                +isNull(九月,0)+isNull(十月,0)+isNull(十一月,0)+isNull(十二月,0)) from rpt07 where FAccountID in (6)";

           //dt.Rows[31][2] = SqlHelper.ExecuteScalar(SqlHelper.connStrK3, sql_clfk);

//合计付款
           dt.Rows[32][2] = Convert.ToDecimal(dt.Rows[31][2]) + Convert.ToDecimal(dt.Rows[30][2]);// 


//处理公司列
            for (int i = 0; i < 11; i++)
            {  
                    dt.Rows[i][7] = Convert.ToDecimal(dt.Rows[i][2]) + Convert.ToDecimal(dt.Rows[i][3]) + Convert.ToDecimal(dt.Rows[i][4]) + Convert.ToDecimal(dt.Rows[i][5]) + Convert.ToDecimal(dt.Rows[i][6]); 
            }
            for (int i = 12; i < 19; i++) 
            { 
                    dt.Rows[i][7] = Convert.ToDecimal(dt.Rows[i][2]) + Convert.ToDecimal(dt.Rows[i][3]) + Convert.ToDecimal(dt.Rows[i][4]) + Convert.ToDecimal(dt.Rows[i][5]) + Convert.ToDecimal(dt.Rows[i][6]); 
            }
           

            dt.Rows[11][7] = Convert.ToDecimal( dt.Rows[10][7] ) / Convert.ToDecimal(dt.Rows[3][7]);//项目利润率
            dt.Rows[19][7] = Convert.ToDecimal(dt.Rows[18][7]) / Convert.ToDecimal(dt.Rows[3][7]);//利润率
            dt.Rows[20][7] = btsr;//补贴收入
            dt.Rows[21][7] = clft;//车轮分摊-公司
            
            dt.Rows[22][7] = Convert.ToDecimal(dt.Rows[22][2]) + Convert.ToDecimal(dt.Rows[22][3]) + Convert.ToDecimal(dt.Rows[22][4]) + Convert.ToDecimal(dt.Rows[22][5]) + Convert.ToDecimal(dt.Rows[22][6]); //利润22-公司
            dt.Rows[23][7] = Convert.ToDecimal(dt.Rows[22][7]) / Convert.ToDecimal(dt.Rows[3][7]);//利润率23
            for (int i = 24; i < 26; i++)//研发费用，利润25
            {
                dt.Rows[i][7] = Convert.ToDecimal(dt.Rows[i][2]) + Convert.ToDecimal(dt.Rows[i][3]) + Convert.ToDecimal(dt.Rows[i][4]) + Convert.ToDecimal(dt.Rows[i][5]) + Convert.ToDecimal(dt.Rows[i][6]);
            }
            dt.Rows[26][7] = Convert.ToDecimal(dt.Rows[25][7]) / Convert.ToDecimal(dt.Rows[3][7]);//利润率26

            //处理车轮列
            dt.Rows[0][8] = dt_cl.Select("kmid=1074")[0]["summonth"];
            dt.Rows[1][8] = dt_cl.Select("kmid=1")[0]["summonth"];
            //dt.Rows[2][8] = 0;
            //dt.Rows[3][8] = 0;
            dt.Rows[4][8] = dt_cl.Select("kmid=1133")[0]["summonth"];
            dt.Rows[5][8] = dt_cl.Select("kmid=1192")[0]["summonth"];
            dt.Rows[6][8] = dt_cl.Select("kmid=1136 or kmid=1146 ")[0]["summonth"];//易耗品=易耗+折旧
            dt.Rows[7][8] = dt_cl.Select("kmid=1145 or kmid=3 ")[0]["summonth"];//运输费=运输费+退款金额
            //制造费用
            dt.Rows[9][8] =Convert.ToDecimal( dt.Rows[4][8] ) + Convert.ToDecimal(dt.Rows[5][8]) + Convert.ToDecimal(dt.Rows[6][8])+ Convert.ToDecimal(dt.Rows[7][8]);
            //项目利润
            dt.Rows[10][8] = Convert.ToDecimal(dt.Rows[0][8]) - Convert.ToDecimal(dt.Rows[9][8]);
            //项目利润率
            dt.Rows[11][8] = Convert.ToDecimal(dt.Rows[10][8])/ Convert.ToDecimal(dt.Rows[0][8]);

            dt.Rows[13][8] = dt_cl.Select("kmid=1185")[0]["summonth"];//办公费
            dt.Rows[14][8] = dt_cl.Select("kmid=1185")[0]["summonth"];//差旅费
            dt.Rows[15][8] = dt_cl.Select("kmid=1185")[0]["summonth"];//招待费
            //部门费用
              dt.Rows[16][8] =Convert.ToDecimal( dt.Rows[13][8] ) + Convert.ToDecimal(dt.Rows[14][8]) + Convert.ToDecimal(dt.Rows[15][8]);
            dt.Rows[21][8] = clft;//车轮分摊-合计
            //利润
             dt.Rows[22][8] =Convert.ToDecimal( dt.Rows[10][8] )- Convert.ToDecimal(dt.Rows[16][8]) - Convert.ToDecimal(dt.Rows[21][8]);
            //利润率
             dt.Rows[23][8] = Convert.ToDecimal(dt.Rows[22][8]) / Convert.ToDecimal(dt.Rows[0][8]);

            //处理合计列
             dt.Rows[0][9] = Convert.ToDecimal(dt.Rows[0][7]) + Convert.ToDecimal(dt.Rows[0][8]);
             dt.Rows[1][9] = Convert.ToDecimal(dt.Rows[1][7]) + Convert.ToDecimal(dt.Rows[1][8]);

            string jsondt = JsonHelper.ToJson(dt);
            return Content(jsondt);

        }


        /// <summary>
        /// 财务、销售等成本子报表查询
        /// </summary>
        /// <returns></returns>
        public ActionResult getExpenseRpt()
        {
            //string groupType = Request["grouptype"] == null ? "dept" : Request["grouptype"];//projectNo
            string status = Request["status"] == null ? "all" : Request["status"];//all，0，1
            //string period = Request["period"] == null ? "all" : Request["period"];//“2020-01”
            string periodType = Request["periodType"] == null ? "claimTime" : Request["periodType"];//claimPeriod 
            string dept = Request["dept"] == null ? "nyhb" : Request["dept"];//部门
            string type = Request["type"] == null ? "xiaoshou" : Request["type"];//销售、财务、
            string deptRealName="能源环保事业部";
            if (dept=="nyhb")
	            {
		             deptRealName="能源环保事业部";
	            }
                       

             DataTable dt = RptBLL.baoxiaofeiyong(deptRealName, periodType, status);
            DataTable dt2 = dt.Clone();
            // DataRow[] drs;
            switch (type)
            {
                case "xiaoshou":
                  //  drs = dt.Select("项目 like '销售费用_%'"); 
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["项目"].ToString().Contains("销售费用") && dr["项目"].ToString().Length>4)
                        {
                            dt2.ImportRow(dr);
                        }                       
                    }
                    break;
                case "guanli": 
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["项目"].ToString().Contains("管理费用") && dr["项目"].ToString().Length > 4)
                        {
                            dt2.ImportRow(dr);
                        }        
                    }
                    break;
                case "caiwu": 
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["项目"].ToString().Contains("财务费用") )
                        {
                            dt2.ImportRow(dr);
                        }
                    }
                    break;
                  
                default:
                    break;
            } 

            string jsondt = JsonHelper.ToJson(dt2);
            return Content(jsondt);
           
        }
     

    }
}
